IF OBJECT_ID('homeland', 'V') IS NOT NULL
	DROP VIEW homeland
GO
CREATE VIEW homeland AS
(SELECT country, COUNT(PERSON.id) AS people,
	ISNULL ((SELECT COUNT(PLACE_TOUR.id) /  (YEAR(MAX(end_date)) - YEAR(MIN(start_date)) + 1)
		FROM PLACE_TOUR
			JOIN PLACE_TIME ON (PLACE_TIME.place_id = PLACE_TOUR.id)
			JOIN [GROUP] ON ([GROUP].id = PLACE_TIME.group_id)
			JOIN PERSON_GROUP ON (PERSON_GROUP.group_id = [GROUP].id)
			JOIN PERSON ON (PERSON.id = PERSON_GROUP.person_id)
			JOIN PASSPORT ON (PASSPORT.person_id = PERSON.id)
		WHERE ((PASSPORT.country_id = X.id) AND (international = 0) AND (PLACE_TOUR.country_id = PERSON.country_id))
		), 0) AS cnt
	FROM COUNTRY X
		JOIN PASSPORT ON (PASSPORT.country_id = X.id)
		JOIN PERSON ON (PERSON.id = PASSPORT.person_id)
	WHERE (international = 0)
	GROUP BY country, X.id
)
GO
SELECT * 
	FROM homeland